﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;

namespace Streaming_Service.SQL_Ting {
    using System.Data;

    using Streaming_Service.Media;

    public class GenreQueries {
        public static bool AddGenre(string genre, SqlHandler sh) {
            if (string.IsNullOrEmpty(genre) || GenreExists(genre, sh)) return false;

            var SQL = "INSERT INTO genre (name) VALUES(@name)";

            var c = sh.CreateCommand(SQL);
            c.Parameters.Add("name", SqlDbType.VarChar).Value = genre;

            var r = c.ExecuteNonQuery();

            return r > 0;
        }

        public static GenreEntry GetGenre(string genre, SqlHandler sh) {
            var SQL = "SELECT * FROM genre WHERE name = @name";

            var c = sh.CreateCommand(SQL);
            c.Parameters.Add("name", SqlDbType.VarChar).Value = genre;

            var entry = new GenreEntry();

            var r = c.ExecuteReader();
            if(r.Read()) {
                entry.Id = r.GetInt32(r.GetOrdinal("id"));
                entry.Name = r.GetString(r.GetOrdinal("name"));
            }
            r.Close();

            return entry;
        }

        public static bool GenreExists(string genre, SqlHandler sh) {
            var SQL = "SELECT id FROM genre WHERE name = @name";

            var c = sh.CreateCommand(SQL);
            c.Parameters.Add("name", SqlDbType.VarChar).Value = genre;

            var r = c.ExecuteScalar();
            if(r is int) {
                return (int)r > 0;
            }
            return false;
        }

        public static bool GenreExists(int genreId, SqlHandler sh) {
            var SQL = "SELECT id FROM genre WHERE id = @id";

            var c = sh.CreateCommand(SQL);
            c.Parameters.Add("id", SqlDbType.Int).Value = genreId;

            var r = c.ExecuteScalar();
            if (r is int) {
                return (int)r > 0;
            }
            return false;
        }

        public static List<GenreEntry> ListGenres(SqlHandler sh) {
            var SQL = "SELECT * FROM genre";

            var genres = new List<GenreEntry>();

            var c = sh.CreateCommand(SQL);

            var r = c.ExecuteReader();
            while(r.Read()) {
                genres.Add(
                    new GenreEntry {
                        Id = r.GetInt32(r.GetOrdinal("id")), 
                        Name = r.GetString(r.GetOrdinal("name"))
                    }
                );
            }
            r.Close();

            return genres;
        } 

        public static bool DeleteGenre(int genreId, SqlHandler sh) {
            var SQL = "DELETE FROM genre WHERE id = @id";

            var c = sh.CreateCommand(SQL);
            c.Parameters.Add("id", SqlDbType.Int).Value = genreId;

            var r = c.ExecuteNonQuery();

            return r > 0;
        }

        public static bool RenameGenre(int genreId, string newName, SqlHandler sh) {
            if (string.IsNullOrEmpty(newName) || GenreExists(newName, sh)) return false;
            var SQL = "UPDATE genre SET name = @name WHERE id = @id";

            var c = sh.CreateCommand(SQL);
            c.Parameters.Add("id", SqlDbType.Int).Value = genreId;
            c.Parameters.Add("name", SqlDbType.VarChar).Value = newName;

            var r = c.ExecuteNonQuery();

            return r > 0;
        }

        public static bool AddMovieGenre(int movieId, int genreId, SqlHandler sh) {
            if (MovieGenreExists(movieId, genreId, sh)) return false;
            if (!MovieQueries.MovieExists(movieId, sh)) return false;
            if (!GenreExists(genreId, sh)) return false;

            var SQL = "INSERT INTO moviegenre (movieid, genreid) VALUES(@movieid, @genreid)";

            var c = sh.CreateCommand(SQL);
            c.Parameters.Add("movieid", SqlDbType.Int).Value = movieId;
            c.Parameters.Add("genreId", SqlDbType.Int).Value = genreId;

            var r = c.ExecuteNonQuery();

            return r > 0;
        }

        public static bool MovieGenreExists(int movieId, int genreId, SqlHandler sh) {
            var SQL = "SELECT movieId FROM moviegenre WHERE movieId = @movieid AND genreId = @genreid";

            var c = sh.CreateCommand(SQL);
            c.Parameters.Add("movieid", SqlDbType.Int).Value = movieId;
            c.Parameters.Add("genreId", SqlDbType.Int).Value = genreId;

            var r = c.ExecuteScalar();
            if(r is int) {
                return (int)r == movieId;
            }

            return false;
        }

        public static bool DeleteMovieGenre(int movieId, int genreId, SqlHandler sh) {
            var SQL = "DELETE FROM moviegenre WHERE movieid = @movieid AND genreid = @genreid";

            var c = sh.CreateCommand(SQL);
            c.Parameters.Add("movieid", SqlDbType.Int).Value = movieId;
            c.Parameters.Add("genreId", SqlDbType.Int).Value = genreId;

            var r = c.ExecuteNonQuery();

            return r > 0;
        }
    }
}